package com.tencent.supersonic.chat.core.parser.aggregate;

import com.alibaba.fastjson.JSON;
import com.tencent.supersonic.headless.core.pojo.QueryStatement;
import com.tencent.supersonic.headless.core.translator.parser.OntologyQueryParser;
import org.junit.jupiter.api.Test;
import org.testng.Assert;

public class CalciteSqlParserTest {

    @Test
    public void testCalciteSqlParser() throws Exception {
        String json = "{\n" + "    \"dataSetId\": 1,\n" + "    \"sql\": \"\",\n"
                + "    \"sourceId\": \"\",\n" + "    \"errMsg\": \"\",\n"
                + "    \"metricQueryParam\": {\n" + "        \"metrics\": [\n"
                + "            \"pv\"\n" + "        ],\n" + "        \"dimensions\": [\n"
                + "            \"sys_imp_date\"\n" + "        ],\n"
                + "        \"nativeQuery\": false\n" + "    },\n" + "    \"status\": 0,\n"
                + "    \"isS2SQL\": false,\n" + "    \"enableOptimize\": true,\n"
                + "    \"minMaxTime\": {\n" + "        \"left\": \"sys_imp_date\",\n"
                + "        \"middle\": \"2024-03-24\",\n" + "        \"right\": \"2024-03-18\"\n"
                + "    },\n"
                + "    \"dataSetSql\": \"SELECT sys_imp_date, SUM(pv) AS pv FROM t_1 WHERE "
                + "sys_imp_date >= '2024-03-18' AND sys_imp_date <= '2024-03-24' GROUP BY sys_imp_date LIMIT 365\",\n"
                + "    \"dataSetAlias\": \"t_1\",\n" + "    \"dataSetSimplifySql\": \"\",\n"
                + "    \"enableLimitWrapper\": false,\n" + "    \"semanticModel\": {\n"
                + "        \"schemaKey\": \"VIEW_1\",\n" + "        \"metrics\": [\n"
                + "            {\n" + "                \"name\": \"pv\",\n"
                + "                \"owners\": [\n" + "                    \"admin\"\n"
                + "                ],\n" + "                \"type\": \"ATOMIC\",\n"
                + "                \"metricTypeParams\": {\n"
                + "                    \"measures\": [\n" + "                        {\n"
                + "                            \"name\": \"s2_pv_uv_statis_pv\",\n"
                + "                            \"agg\": \"SUM\",\n"
                + "                            \"constraint\": \"\"\n"
                + "                        }\n" + "                    ],\n"
                + "                    \"isFieldMetric\": false,\n"
                + "                    \"expr\": \"s2_pv_uv_statis_pv\"\n" + "                }\n"
                + "            },\n" + "            {\n" + "                \"name\": \"uv\",\n"
                + "                \"owners\": [\n" + "                    \"admin\"\n"
                + "                ],\n" + "                \"type\": \"DERIVED\",\n"
                + "                \"metricTypeParams\": {\n"
                + "                    \"measures\": [\n" + "                        {\n"
                + "                            \"name\": \"user_id\",\n"
                + "                            \"expr\": \"user_id\"\n"
                + "                        }\n" + "                    ],\n"
                + "                    \"isFieldMetric\": true,\n"
                + "                    \"expr\": \"user_id\"\n" + "                }\n"
                + "            },\n" + "            {\n" + "                \"name\": \"pv_avg\",\n"
                + "                \"owners\": [\n" + "                    \"admin\"\n"
                + "                ],\n" + "                \"type\": \"DERIVED\",\n"
                + "                \"metricTypeParams\": {\n"
                + "                    \"measures\": [\n" + "                        {\n"
                + "                            \"name\": \"pv\",\n"
                + "                            \"expr\": \"pv\"\n" + "                        },\n"
                + "                        {\n" + "                            \"name\": \"uv\",\n"
                + "                            \"expr\": \"uv\"\n" + "                        }\n"
                + "                    ],\n" + "                    \"isFieldMetric\": true,\n"
                + "                    \"expr\": \"pv\"\n" + "                }\n"
                + "            },\n" + "            {\n"
                + "                \"name\": \"stay_hours\",\n" + "                \"owners\": [\n"
                + "                    \"admin\"\n" + "                ],\n"
                + "                \"type\": \"ATOMIC\",\n"
                + "                \"metricTypeParams\": {\n"
                + "                    \"measures\": [\n" + "                        {\n"
                + "                            \"name\": \"s2_stay_time_statis_stay_hours\",\n"
                + "                            \"agg\": \"SUM\",\n"
                + "                            \"constraint\": \"\"\n"
                + "                        }\n" + "                    ],\n"
                + "                    \"isFieldMetric\": false,\n"
                + "                    \"expr\": \"s2_stay_time_statis_stay_hours\"\n"
                + "                }\n" + "            }\n" + "        ],\n"
                + "        \"datasourceMap\": {\n" + "            \"user_department\": {\n"
                + "                \"id\": 1,\n"
                + "                \"name\": \"user_department\",\n"
                + "                \"sourceId\": 1,\n" + "                \"type\": \"h2\",\n"
                + "                \"sqlQuery\": \"select user_name,department from s2_user_department\",\n"
                + "                \"identifiers\": [\n" + "                    {\n"
                + "                        \"name\": \"user_name\",\n"
                + "                        \"type\": \"primary\"\n" + "                    }\n"
                + "                ],\n" + "                \"dimensions\": [\n"
                + "                    {\n" + "                        \"name\": \"department\",\n"
                + "                        \"type\": \"categorical\",\n"
                + "                        \"expr\": \"department\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"department\"\n"
                + "                    }\n" + "                ],\n"
                + "                \"measures\": [\n" + "                    {\n"
                + "                        \"name\": \"user_department_internal_cnt\",\n"
                + "                        \"agg\": \"count\",\n"
                + "                        \"expr\": \"user_name\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"user_name\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"user_name\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"department\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"department\"\n" + "                    }\n"
                + "                ],\n" + "                \"aggTime\": \"none\"\n"
                + "            },\n" + "            \"s2_pv_uv_statis\": {\n"
                + "                \"id\": 2,\n"
                + "                \"name\": \"s2_pv_uv_statis\",\n"
                + "                \"sourceId\": 1,\n" + "                \"type\": \"h2\",\n"
                + "                \"sqlQuery\": \"SELECT imp_date, user_name, page, 1 as pv, user_name as user_id "
                + "FROM s2_pv_uv_statis\",\n" + "                \"identifiers\": [\n"
                + "                    {\n" + "                        \"name\": \"user_name\",\n"
                + "                        \"type\": \"primary\"\n" + "                    }\n"
                + "                ],\n" + "                \"dimensions\": [\n"
                + "                    {\n" + "                        \"name\": \"imp_date\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"imp_date\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"true\",\n"
                + "                            \"timeGranularity\": \"day\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"imp_date\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"page\",\n"
                + "                        \"type\": \"categorical\",\n"
                + "                        \"expr\": \"page\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"page\"\n" + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"sys_imp_date\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"imp_date\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"true\",\n"
                + "                            \"timeGranularity\": \"day\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_date\"\n"
                + "                    },\n" + "                    {\n"
                + "                        \"name\": \"sys_imp_week\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"DATE_TRUNC('week',imp_date)\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"false\",\n"
                + "                            \"timeGranularity\": \"week\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_week\"\n"
                + "                    },\n" + "                    {\n"
                + "                        \"name\": \"sys_imp_month\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"FORMATDATETIME(PARSEDATETIME"
                + "(imp_date, 'yyyy-MM-dd'),'yyyy-MM') \",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"false\",\n"
                + "                            \"timeGranularity\": \"month\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_month\"\n"
                + "                    }\n" + "                ],\n"
                + "                \"measures\": [\n" + "                    {\n"
                + "                        \"name\": \"s2_pv_uv_statis_pv\",\n"
                + "                        \"agg\": \"SUM\",\n"
                + "                        \"expr\": \"pv\"\n" + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"s2_pv_uv_statis_user_id\",\n"
                + "                        \"agg\": \"SUM\",\n"
                + "                        \"expr\": \"user_id\"\n" + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"s2_pv_uv_statis_internal_cnt\",\n"
                + "                        \"agg\": \"count\",\n"
                + "                        \"expr\": \"user_name\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"user_name\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"user_name\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"imp_date\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"imp_date\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"page\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"page\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"pv\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"pv\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"user_id\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"user_id\"\n" + "                    }\n"
                + "                ],\n" + "                \"aggTime\": \"day\"\n"
                + "            },\n" + "            \"s2_stay_time_statis\": {\n"
                + "                \"id\": 3,\n"
                + "                \"name\": \"s2_stay_time_statis\",\n"
                + "                \"sourceId\": 1,\n" + "                \"type\": \"h2\",\n"
                + "                \"sqlQuery\": \"select imp_date,user_name,stay_hours"
                + ",page from s2_stay_time_statis\",\n" + "                \"identifiers\": [\n"
                + "                    {\n" + "                        \"name\": \"user_name\",\n"
                + "                        \"type\": \"primary\"\n" + "                    }\n"
                + "                ],\n" + "                \"dimensions\": [\n"
                + "                    {\n" + "                        \"name\": \"imp_date\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"imp_date\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"true\",\n"
                + "                            \"timeGranularity\": \"day\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"imp_date\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"page\",\n"
                + "                        \"type\": \"categorical\",\n"
                + "                        \"expr\": \"page\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"page\"\n" + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"sys_imp_date\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"imp_date\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"true\",\n"
                + "                            \"timeGranularity\": \"day\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_date\"\n"
                + "                    },\n" + "                    {\n"
                + "                        \"name\": \"sys_imp_week\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"DATE_TRUNC('week',imp_date)\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"false\",\n"
                + "                            \"timeGranularity\": \"week\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_week\"\n"
                + "                    },\n" + "                    {\n"
                + "                        \"name\": \"sys_imp_month\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"FORMATDATETIME(PARSEDATETIME"
                + "(imp_date, 'yyyy-MM-dd'),'yyyy-MM') \",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"false\",\n"
                + "                            \"timeGranularity\": \"month\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_month\"\n"
                + "                    }\n" + "                ],\n"
                + "                \"measures\": [\n" + "                    {\n"
                + "                        \"name\": \"s2_stay_time_statis_stay_hours\",\n"
                + "                        \"agg\": \"SUM\",\n"
                + "                        \"expr\": \"stay_hours\"\n" + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"s2_stay_time_statis_internal_cnt\",\n"
                + "                        \"agg\": \"count\",\n"
                + "                        \"expr\": \"user_name\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"user_name\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"user_name\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"imp_date\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"imp_date\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"page\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"page\"\n" + "                    },\n"
                + "                    {\n" + "                        \"name\": \"stay_hours\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"stay_hours\"\n" + "                    }\n"
                + "                ],\n" + "                \"aggTime\": \"day\"\n"
                + "            }\n" + "        },\n" + "        \"dimensionMap\": {\n"
                + "            \"user_department\": [\n" + "                {\n"
                + "                    \"name\": \"department\",\n"
                + "                    \"owners\": \"admin\",\n"
                + "                    \"type\": \"categorical\",\n"
                + "                    \"expr\": \"department\",\n"
                + "                    \"dimensionTimeTypeParams\": {\n"
                + "                    },\n" + "                    \"dataType\": \"UNKNOWN\",\n"
                + "                    \"bizName\": \"department\"\n" + "                }\n"
                + "            ],\n" + "            \"s2_pv_uv_statis\": [\n" + "            ],\n"
                + "            \"s2_stay_time_statis\": [\n" + "                {\n"
                + "                    \"name\": \"page\",\n"
                + "                    \"owners\": \"admin\",\n"
                + "                    \"type\": \"categorical\",\n"
                + "                    \"expr\": \"page\",\n"
                + "                    \"dimensionTimeTypeParams\": {\n"
                + "                    },\n" + "                    \"dataType\": \"UNKNOWN\",\n"
                + "                    \"bizName\": \"page\"\n" + "                }\n"
                + "            ]\n" + "        },\n" + "        \"materializationList\": [\n"
                + "        ],\n" + "        \"joinRelations\": [\n" + "            {\n"
                + "                \"id\": 1,\n"
                + "                \"left\": \"user_department\",\n"
                + "                \"right\": \"s2_pv_uv_statis\",\n"
                + "                \"joinType\": \"left join\",\n"
                + "                \"joinCondition\": [\n" + "                    {\n"
                + "                        \"left\": \"user_name\",\n"
                + "                        \"middle\": \"=\",\n"
                + "                        \"right\": \"user_name\"\n" + "                    }\n"
                + "                ]\n" + "            },\n" + "            {\n"
                + "                \"id\": 2,\n"
                + "                \"left\": \"user_department\",\n"
                + "                \"right\": \"s2_stay_time_statis\",\n"
                + "                \"joinType\": \"left join\",\n"
                + "                \"joinCondition\": [\n" + "                    {\n"
                + "                        \"left\": \"user_name\",\n"
                + "                        \"middle\": \"=\",\n"
                + "                        \"right\": \"user_name\"\n" + "                    }\n"
                + "                ]\n" + "            }\n" + "        ],\n"
                + "        \"database\": {\n" + "            \"id\": 1,\n"
                + "            \"name\": \"数据实例\",\n"
                + "            \"description\": \"样例数据库实例\",\n"
                + "            \"url\": \"jdbc:h2:mem:semantic;DATABASE_TO_UPPER=false\",\n"
                + "            \"username\": \"root\",\n"
                + "            \"password\": \"semantic\",\n" + "            \"type\": \"h2\",\n"
                + "            \"connectInfo\": {\n"
                + "                \"url\": \"jdbc:h2:mem:semantic;DATABASE_TO_UPPER=false\",\n"
                + "                \"userName\": \"root\",\n"
                + "                \"password\": \"semantic\"\n" + "            },\n"
                + "            \"admins\": [\n" + "            ],\n"
                + "            \"viewers\": [\n" + "            ],\n"
                + "            \"createdBy\": \"admin\",\n"
                + "            \"updatedBy\": \"admin\",\n"
                + "            \"createdAt\": 1711367511146,\n"
                + "            \"updatedAt\": 1711367511146\n" + "        }\n" + "    }\n" + "}";
        // QueryStatement queryStatement = JSON.parseObject(json, QueryStatement.class);
        // OntologyQueryParser calciteSqlParser = new OntologyQueryParser();
        // calciteSqlParser.parse(queryStatement);
        // Assert.assertEquals(queryStatement.getSql().trim().replaceAll("\\s+", ""),
        // "SELECT`imp_date`AS`sys_imp_date`,SUM(1)AS`pv`" + "FROM" + "`s2_pv_uv_statis`"
        // + "GROUPBY`imp_date`,`imp_date`");
    }
}
